[新機能] Amazon Aurora PostgreSQL と Amazon Redshift の Zero-ETL 統合の試してみました

$ psql -h zeroetlauroraredshiftstac-auroraclusterwriteraa1ab-wz0ssq9a2c6t.cwufmclyfzur.ap-northeast-1.rds.amazonaws.com -U postgres -d postgres -p 5432
Password for user postgres: 
psql (15.8, server 16.4)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> \d
Did not find any relations.
postgres=> 
postgres=> 
postgres=> \dl
      Large objects
 ID | Owner | Description 
----+-------+-------------
(0 rows)

postgres=> create table devices1 (id varchar(16), data_ts varchar(32));
CREATE TABLE
postgres=> create table devices2 (id varchar(16), data_ts varchar(32));
CREATE TABLE
postgres=> create table devices3 (id varchar(16), data_ts varchar(32));
CREATE TABLE
postgres=> \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | devices1 | table | postgres
 public | devices2 | table | postgres
 public | devices3 | table | postgres
(3 rows)

postgres=> create table devices1 (id varchar(16), data_ts varchar(32) PRIMARY KEY);
CREATE TABLE
postgres=> create table devices2 (id varchar(16), data_ts varchar(32) PRIMARY KEY);
CREATE TABLE
postgres=> create table devices3 (id varchar(16), data_ts varchar(32) PRIMARY KEY);
CREATE TABLE

import * as cdk from 'aws-cdk-lib';
import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as rds from 'aws-cdk-lib/aws-rds';
import * as redshift from 'aws-cdk-lib/aws-redshift';
import * as iam from 'aws-cdk-lib/aws-iam';
import * as secretsmanager from 'aws-cdk-lib/aws-secretsmanager';
import { Construct } from 'constructs';

export class ZeroEtlAuroraRedshiftStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    // VPCの作成
    const vpc = new ec2.Vpc(this, 'ZeroEtlVpc', {
      maxAzs: 2,
    });

    // Redshift用のセキュリティグループを作成
    const redshiftSecurityGroup = new ec2.SecurityGroup(this, 'RedshiftSecurityGroup', {
      vpc,
      description: 'Security group for Redshift cluster',
      allowAllOutbound: true,
    });

    // Aurora PostgreSQLのパラメータグループの作成
    const auroraParamGroup = new rds.ParameterGroup(this, 'AuroraParamGroup', {
      engine: rds.DatabaseClusterEngine.auroraPostgres({ version: rds.AuroraPostgresEngineVersion.VER_16_4 }),
      parameters: {
        'rds.logical_replication': '1',
        'aurora.enhanced_logical_replication': '1',
        'aurora.logical_replication_backup': '0',
        'aurora.logical_replication_globaldb': '0',
      },
    });

    // Aurora PostgreSQLクラスターの作成
    const auroraCluster = new rds.DatabaseCluster(this, 'AuroraCluster', {
      engine: rds.DatabaseClusterEngine.auroraPostgres({ version: rds.AuroraPostgresEngineVersion.VER_16_4 }),
      vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS },
      writer: rds.ClusterInstance.provisioned('Writer', {
        instanceType: ec2.InstanceType.of(ec2.InstanceClass.BURSTABLE3, ec2.InstanceSize.MEDIUM),
      }),
      readers: [
        rds.ClusterInstance.provisioned('Reader1', {
          instanceType: ec2.InstanceType.of(ec2.InstanceClass.BURSTABLE3, ec2.InstanceSize.MEDIUM),
        }),
      ],
    });

    // Redshiftのパラメータグループの作成
    const redshiftParamGroup = new redshift.CfnClusterParameterGroup(this, 'RedshiftParamGroup', {
      description: 'Parameter group for Redshift',
      parameterGroupFamily: 'redshift-1.0',
      parameters: [
        {
          parameterName: 'enable_case_sensitive_identifier',
          parameterValue: 'true',
        },
      ],
    });

    // Redshift用のIAMロールの作成
    const redshiftRole = new iam.Role(this, 'RedshiftRole', {
      assumedBy: new iam.ServicePrincipal('redshift.amazonaws.com'),
      managedPolicies: [
        iam.ManagedPolicy.fromAwsManagedPolicyName('AmazonRedshiftAllCommandsFullAccess'),
        iam.ManagedPolicy.fromAwsManagedPolicyName('AmazonS3FullAccess'),
      ],
    });

    redshiftRole.addToPolicy(new iam.PolicyStatement({
      actions: ['sts:AssumeRole'],
      resources: [auroraCluster.clusterArn],
    }));

    // Redshift用のシークレットの作成
    const excludeRedshiftPasswordCharacters = '/@"\'\\:'
    const redshiftSecret = new secretsmanager.Secret(this, 'RedshiftSecret', {
      generateSecretString: {
        excludeCharacters: excludeRedshiftPasswordCharacters,
        generateStringKey: 'password',
        passwordLength: 32,
        requireEachIncludedType: true,
        secretStringTemplate: JSON.stringify({
          engine: 'redshift',
          username: 'root',
          port: 5439,
          dbname: 'dev'
        }),
        excludePunctuation: true,
        includeSpace: false,
      },
    });

    // Redshiftクラスターの作成
    const redshiftCluster = new redshift.CfnCluster(this, 'RedshiftCluster', {
      clusterType: 'single-node',
      nodeType: 'ra3.large',
      masterUsername: 'root',
      masterUserPassword: redshiftSecret.secretValueFromJson('password').unsafeUnwrap(),
      dbName: 'dev',
      vpcSecurityGroupIds: [redshiftSecurityGroup.securityGroupId],
      clusterParameterGroupName: redshiftParamGroup.ref,
      iamRoles: [redshiftRole.roleArn],
      encrypted: true,
      publiclyAccessible: false,
      clusterSubnetGroupName: new redshift.CfnClusterSubnetGroup(this, 'RedshiftSubnetGroup', {
        description: 'Subnet group for Redshift cluster',
        subnetIds: vpc.selectSubnets({ subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS }).subnetIds,
      }).ref,
    });

    // 出力
    new cdk.CfnOutput(this, 'AuroraClusterEndpoint', {
      value: auroraCluster.clusterEndpoint.hostname,
      description: 'Aurora Cluster Endpoint',
    });

    new cdk.CfnOutput(this, 'RedshiftClusterEndpoint', {
      value: redshiftCluster.attrEndpointAddress,
      description: 'Redshift Cluster Endpoint',
    });
  }
}